/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models;

import controllers.entity.GiaoTrinh;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author hoangnv
 */
public class GiaoTrinhDAO {
    private PreparedStatement prst;
    public List <GiaoTrinh>  getAllGiaoTrinhByMaCanBo(int MaCanBo){
        List <GiaoTrinh> list = new ArrayList<>();
        boolean ok = DBConnect.Connect();
        if (!ok) {
            DBConnect.Close();
            return null;
        }
        try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("SELECT * FROM GiaoTrinh WHERE MaCanBo = ?");
            prst.setInt(1, MaCanBo);
            ResultSet rs = prst.executeQuery();
            while (rs.next()) {
                int maCanBo = rs.getInt("MaCanBo");
                String tenGiaoTrinh  = rs.getString("TenGiaoTrinh");
                String tenTacGia  = rs.getString("TenTacGia");
                String nhaXuatBan = rs.getString("NhaXuatBan");
                int year = rs.getInt("year");
                String magiaotrinh=rs.getString("MaGiaoTrinh");
                        
                GiaoTrinh temp = new GiaoTrinh(maCanBo,tenTacGia,tenGiaoTrinh,year,nhaXuatBan,magiaotrinh);      
                
                list.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        DBConnect.Close();
        return list;
    }
    public List <GiaoTrinh>  getAllGiaoTrinh(){
        List <GiaoTrinh> list = new ArrayList<>();
        boolean ok = DBConnect.Connect();
        if (!ok) {
            DBConnect.Close();
            return null;
        }
        try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("SELECT * FROM GiaoTrinh");
            
            ResultSet rs = prst.executeQuery();
            while (rs.next()) {
                int maCanBo = rs.getInt("MaCanBo");
                String tenGiaoTrinh  = rs.getString("TenGiaoTrinh");
                String tenTacGia  = rs.getString("TenTacGia");
                String nhaXuatBan = rs.getString("NhaXuatBan");
                int year = rs.getInt("year");
                String magiaotrinh=rs.getString("MaGiaoTrinh");
                        
                GiaoTrinh temp = new GiaoTrinh(maCanBo,tenTacGia,tenGiaoTrinh,year,nhaXuatBan,magiaotrinh);      
                
                list.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        DBConnect.Close();
        return list;
    }
    public int getCountGiaoTrinh(){
        int count = 0;
        List <GiaoTrinh> list = getAllGiaoTrinh();
        for (GiaoTrinh ch : list){
            Integer i = Integer.parseInt(ch.getMaGiaoTrinh());
            if (i > count ) count = i;
        }
        return count;
    }
    public boolean InsertGiaoTrinh(GiaoTrinh X){
       boolean ok = DBConnect.Connect();
       if (!ok) {
            DBConnect.Close();
            return false;
       }
       try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("INSERT INTO "
                    + "GiaoTrinh(MaCanBo,TenTacGia,TenGiaoTrinh,year,NhaXuatBan,MaGiaoTrinh)"
                    + "VALUES (?,?,?,?,?,?)");
            prst.setInt(1, X.getMaCanBo());
            prst.setString(2,X.getTenTacGia());
            prst.setString(3,X.getTenGiaoTrinh());
            prst.setInt(4,X.getYear());
            prst.setString(5,X.getNhaXuatBan());
            prst.setString(6,X.getMaGiaoTrinh());
            if (prst.executeUpdate() >0) {
                DBConnect.Close();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
       DBConnect.Close();
       return false;
    }
    public boolean UpdateGiaoTrinh(GiaoTrinh X){
       boolean ok = DBConnect.Connect();
       if (!ok) {
            DBConnect.Close();
            return false;
       }
       try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("UPDATE GiaoTrinh SET MaCanBo = ? , TenTacGia = ? , TenGiaoTrinh = ?, year = ?, NhaXuatBan = ? WHERE MaGiaoTrinh=?");
                  
            prst.setInt(1, X.getMaCanBo());
            prst.setString(2,X.getTenTacGia());
            prst.setString(3,X.getTenGiaoTrinh());
            prst.setInt(4,X.getYear());
            prst.setString(5,X.getNhaXuatBan());
            prst.setString(6,X.getMaGiaoTrinh());
            if (prst.executeUpdate() > 0) {
                DBConnect.Close();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
       DBConnect.Close();
       return false;
    }
    public boolean DeleteGiaoTrinhByMaGiaoTrinh(String MaGiaoTrinh){
       boolean ok = DBConnect.Connect();
       if (!ok) {
            DBConnect.Close();
            return false;
       }
       try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("DELETE FROM GiaoTrinh WHERE MaGiaoTrinh = ? ");
            prst.setString(1,MaGiaoTrinh);
            if (prst.executeUpdate() > 0) {
                DBConnect.Close();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
       DBConnect.Close();
       return false;
    } 
}
